<?
class maestro_reporte{

	// Propiedades
	var $id;
	var $descripcion;
	var $archivo;
	var $parametros;

	var $total;

	function get($conn, $id){
		try {
			$q = "SELECT A.* FROM reportes AS A WHERE A.id=$id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->descripcion = $r->fields['descripcion'];
				$this->archivo = $r->fields['archivo'];

				$q = "SELECT  * FROM reporte_parametros AS A 
					WHERE id_reporte = $id";
				$rL = $conn->Execute(utf8_encode($q));

				$i = 0;
				while(!$rL->EOF){
					$ParametrosAux[$i][0] = $rL->fields['descripcion'];
					$ParametrosAux[$i][1] = $rL->fields['parametro'];
					$ParametrosAux[$i][2] = $rL->fields['tipo'];
					$ParametrosAux[$i][3] = $rL->fields['orden'];

					$i++;

					$rL->movenext();
				}
				$this->parametros = new Services_JSON();
				$this->parametros = (is_array($ParametrosAux) ? $this->parametros->encode($ParametrosAux) : false);

			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function get_all($conn, $orden="descripcion", $Tipo, $Valor, $TipoE, $max=10, $from=1,$id_zona){
		try {
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			
			if(empty($Valor)){
				$q = "SELECT id FROM reportes  ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
				$q = "SELECT id FROM reportes  ";
				$q.= "WHERE descripcion::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);			

			}
			elseif($Tipo==1){
				$q = "SELECT id FROM reportes ";
				$q.= "WHERE archivo::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}

			//die($q);			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new maestro_reporte;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function get_all_by_zona($conn, $orden="A.despcripcion", $zona){
		$q = "SELECT A.* FROM reportes AS A ";
		$q.= " INNER JOIN relacion_of_rep AS B ON A.id = B.id_reporte ";
		$q.= "WHERE B.id_zona = $zona ";
		$q.= "ORDER BY $orden ";

//		die($q);

		$r = $conn->Execute($q);
		$collection=array();
		while(!$r->EOF){
			$ue = new maestro_reporte;
			$ue->get($conn, $r->fields['id']);
			$coleccion[] = $ue;
			$r->movenext();
		}
		$this->total = $r->RecordCount();
		return $coleccion;
	}


	function add($conn, $descripcion, $archivo, $parametros){
		try {
			$q = "INSERT INTO reportes ";
			$q.= "(descripcion, archivo) ";
			$q.= "VALUES ";
			$q.= "(upper('$descripcion'), '$archivo')";
			//$q.= " RETURNING id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));

			$this->auditoria($conn, $q);
			
			$q2 = "SELECT MAX(id) AS id FROM reportes";
			$r2 = $conn->Execute(utf8_encode($q2));
			
			$this->GuardarParametros($conn, $r2->fields['id'], $parametros);
			
			//$this->GuardarParametros($conn, $r->fields['id'], $parametros);
			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function set($conn, $id, $descripcion, $archivo, $parametros){
		try {
			$q = "UPDATE reportes SET descripcion=upper('$descripcion'), archivo='$archivo' ";
			$q.= "WHERE id=$id";	
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);

			$this->GuardarParametros($conn, $id, $parametros);

			return REG_SET_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function del($conn, $id){
		try {
			$q = "DELETE FROM reportes WHERE id='$id'";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function GuardarParametros($conn,$id,$parametros){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$parametros));
			$q = "DELETE FROM reporte_parametros WHERE id_reporte=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->parametros)){
				foreach($JsonRec->parametros as $parametrosAux){
					$q = "INSERT INTO reporte_parametros ";
					$q.= "(id_reporte, descripcion, parametro, tipo, orden) ";
					$q.= "VALUES ";
					$q.= "($id, upper('$parametrosAux[0]'), '$parametrosAux[1]','$parametrosAux[2]', NULLIF('$parametrosAux[3]','')::integer) ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}


	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="id", $id_zona)
	{
		$TipoE = empty($TipoE) ? 0 : $TipoE;
		if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM reportes ";
		}
		elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM reportes ";
			$q.= " WHERE descripcion::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM reportes ";
			$q.= " WHERE archivo::text ILIKE '%$Valor%'::text";
		}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}

	function has_permiso($conn, $id_usuario, $id_reporte){
		$q = "SELECT id ";
		$q.= "FROM relacion_us_rep  ";
		$q.= "WHERE id_usuario='$id_usuario' AND id_reporte = '$id_reporte' ";
		//die($q);
		//echo $q."<br/>";
		$r = $conn->Execute($q);
		if(!$r->EOF)
			return true;
		else
			return false;
	}

	function oficina_has_permiso($conn, $id_zona, $id_reporte){
		$q = "SELECT id ";
		$q.= "FROM relacion_of_rep  ";
		$q.= "WHERE id_reporte=$id_reporte AND id_zona = $id_zona ";
		//die($q);
		//echo $q."<br/>";
		$r = $conn->Execute($q);
		if(!$r->EOF)
			return true;
		else
			return false;
	}

	function del_permiso($conn, $id_usuario, $id_reporte){
		$q = "DELETE FROM relacion_us_rep WHERE id_usuario='$id_usuario' AND id_reporte = '$id_reporte' ";
		if($conn->Execute($q))
			return true;
		else
			return false;
	}

	function oficina_del_permiso($conn, $id_zona, $id_reporte){
		$q = "DELETE FROM relacion_of_rep WHERE id_zona=$id_zona AND id_reporte = $id_reporte ";
		if($conn->Execute($q))
			return true;
		else
			return false;
	}


	function add_permiso($conn, $id_usuario, $id_reporte){
		$this->del_permiso($conn, $id_usuario, $id_reporte);
		$q = "INSERT INTO relacion_us_rep ";
		$q.= "(id_usuario, id_reporte) ";
		$q.= "VALUES ";
		$q.= "('$id_usuario', '$id_reporte' ) ";
		if($conn->Execute($q))
			return true;
		else
			return false;
	}

	function oficina_add_permiso($conn, $id_zona, $id_reporte){
		$this->oficina_del_permiso($conn, $id_zona, $id_reporte);
		$q = "INSERT INTO relacion_of_rep ";
		$q.= "(id_zona, id_reporte) ";
		$q.= "VALUES ";
		$q.= "($id_zona, $id_reporte ) ";
		if($conn->Execute($q))
			return true;
		else
			return false;
	}
	
	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}	

}
?>
